﻿<cfsilent>
	<cfscript>
		
		markAdvice = getProperty("serviceFactory").getBean("markAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		depId = sessionAdvice.getUserProp("teacherDepartment");
		
		subjectId = event.getArg('SubID');
		grade = event.getArg('Grade');
		
		sql = "SELECT a.sbj_name 
				FROM t_subject a 
				WHERE a.sbj_id = :subjectId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
		rs_subject= queryObj.execute( sql=sql ).getResult();
		
		/* 下载文件名 */
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_subject.sbj_name & "专业," & grade & "级-学生成绩明细", "utf-8");
		

		/* 学年列表 */
		sql = "select 
					t_term.academic_year, count(*) mark_count
				from   t_student_mark a  
					   INNER JOIN t_term ON t_term.term_id = a.term_id  
					   INNER JOIN t_student ON t_student.stu_id = a.stu_id 
					   INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					   INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id 
				where 
					t_subject.sbj_id = :subjectId 
					and 
					t_class.grade = :grade 
					and 
					t_subject.institute_id = :depId 
				group by 
					t_term.academic_year
				order by 
					t_term.academic_year ";
					
		queryObj = new Query( datasource=application.dnsSlave) ;
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		
		rs_terms = queryObj.execute( sql=sql ).getResult();
		
		/* 生成电子表格对象 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");

		spreadsheetObj = excel.SpreadSheetNew( "学年成绩统计", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学年,成绩数量");
		
		for ( a=1; a LTE rs_terms.recordCount; a++ ) {
			
			/* 写入学期统计数据 */
			rowData = rs_terms["academic_year"][a] & "," & rs_terms["mark_count"][a];
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		

		for ( a=1; a LTE rs_terms.recordCount; a++  ) {

			/* 按学年建立工作簿记录班级学生成绩 */
			excel.SpreadsheetCreateSheet( spreadsheetObj, rs_terms["academic_year"][a] & "学年" );

			excel.SpreadsheetSetActiveSheet( spreadsheetObj, rs_terms["academic_year"][a] & "学年" );
			
			termYear = rs_terms["academic_year"][a];

			/* 读取该学年学生成绩 */
			sql = "SELECT 
					  b.academic_term,c.course_name,
					  c.course_credit,a.stu_id,
					  t_student.stu_name,t_class.cls_name,
					  d.prop_name,a.retaking,
					  c.mark_mode,a.mark_result,
					  a.mark_note,a.mark_resit,
					  a.mark_renote,a.mark_resit_limit 
				FROM t_student_mark a 
					 INNER JOIN t_term b ON a.term_id = b.term_id 
					 INNER JOIN t_course c ON a.cid = c.cid 
					 INNER JOIN t_course_prop d ON a.prop_id = d.prop_id  
					 INNER JOIN t_student ON t_student.stu_id = a.stu_id 
					 INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					 INNER JOIN t_subject ON t_class.sbj_id = t_subject.sbj_id  
				WHERE 
					t_class.sbj_id = :subjectId 
					AND 
					t_class.grade = :grade 
				    AND
				    b.academic_year = :termYear 
				ORDER BY b.academic_term,c.course_name,a.stu_id ";
				
			queryObj = new Query( datasource=application.dnsSlave ) ;
			queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="termYear", value=termYear, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			
			rs_mark= queryObj.execute( sql=sql ).getResult();									
			
			/* 添加表头 */
			excel.SpreadsheetAddrow(spreadsheetObj, "学期,课程,学分,学号,姓名,班级,课程性质,重修,正考,补考");

			for ( b=1; b LTE rs_mark.recordCount; b++ ) {
				
				rowData = dictAdvice.getTerm( rs_mark["academic_term"][b] ) 
						& "," 
						& rs_mark["course_name"][b]
						& "," 
						& numberFormat( rs_mark["course_credit"][b], "_.__" )
						& "," 
						& rs_mark["stu_id"][b]
						& "," 
						& rs_mark["stu_name"][b]
						& "," 
						& rs_mark["cls_name"][b]
						& "," 
						& rs_mark["prop_name"][b];
				
				if ( rs_mark["retaking"][b] eq "1" ) {
					rowData = rowData & ",是,"; 
				}
				else {
					rowData = rowData & ", ,"; 
				}
				
				finalResult = markAdvice.getMark( rs_mark["mark_mode"][b], rs_mark["mark_result"][b], rs_mark["mark_note"][b] );
				resitResult = markAdvice.getMarkResit( rs_mark["mark_mode"][b], rs_mark["mark_resit"][b], rs_mark["mark_renote"][b], rs_mark["mark_resit_limit"][b] );
				
				if ( len(finalResult) ) {
					rowData = rowData & finalResult & ",";
				}
				else {
					rowData = rowData & " ,";
				}

				if ( len(resitResult) ) {
					rowData = rowData & resitResult & ",";
				}
				else {
					rowData = rowData & " ,";
				}
				
				/* 写入成绩信息 */
				excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
			}

		}

		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>